﻿
/****** Object:  StoredProcedure [dbo].[Itinerary_saveitinerary] Script Date: 04/20/2008 13:08:00 ******/
CREATE PROC [dbo].[Itinerary_saveitinerary]
	@name nvarchar(256),
	@major bigint,
	@minor bigint,
	@description nvarchar(2048),
	@modifiedby nvarchar(256),
	@modifiedtime datetime,
	@itml xml,
	@user nvarchar(256),
	@altuser nvarchar(256),
	@machine nvarchar(256),
	@status tinyint = 1
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ANSI_PADDING ON

DECLARE @nStatus tinyint
DECLARE @key bigint
DECLARE @idoc int
DECLARE @count int
DECLARE @res int

SELECT @nStatus = nStatus, @key = nItinerarySetID
	FROM [dbo].[Itinerary]
	WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor

IF @@ROWCOUNT = 0
	BEGIN
		-- no row found, so insert away
		INSERT INTO [dbo].[Itinerary] (strName, nMajor, nMinor, nStatus, strDescription, strModifiedBy, dtModifiedTime, imITML)
			VALUES(@name, @major, @minor, @status, @description, @modifiedby, @modifiedtime, @itml)

		-- Add rows to the Itinerary Service table
		INSERT INTO [dbo].[ItineraryService] ([ServiceUuid], [Name], [Type], [IsRequestResponse], [Position], [Stage], [Id], [NextId], [BusinessName],
				[ItineraryName],
				[ItineraryMajorVersion],
				[ItineraryMinorVersion],
				[ModifiedBy],
				[ModifiedOn])
			SELECT 
				Svc.value('(@uuid)[1]', 'NVARCHAR(50)') as 'ServiceUuid',
				Svc.value('(@name)[1]', 'NVARCHAR(100)') as 'ServiceName',
				Svc.value('(@type)[1]', 'NVARCHAR(50)') as 'Type',
				Svc.value('(@isRequestResponse)[1]', 'bit') as 'IsRequestResponse',
				Svc.value('(@position)[1]', 'int') as 'Position',
				Svc.value('(@stage)[1]', 'NVARCHAR(50)') as 'Stage',
				Svc.value('(@id)[1]', 'NVARCHAR(50)') as 'Id',
				Svc.value('(@nextId)[1]', 'NVARCHAR(50)') as 'NextId',
				Svc.value('(@businessName)[1]', 'NVARCHAR(250)') as 'BusinessName',
				@name as 'ItineraryName',
				@major as 'ItineraryMajor',
				@minor as 'ItineraryMinor',
				@modifiedby as 'ModifiedBy',
				@modifiedtime as 'ModifiedOn'
			FROM
				@itml.nodes('//Service') as Itin(Svc)

		-- get back index for new entry
		SELECT @key = nItinerarySetID
			FROM [dbo].[Itinerary]
			WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor

		RETURN @key
	END

-- row found, see if published
-- IF @nStatus = 0
	BEGIN
		-- not published, so we can update it
		UPDATE [dbo].[Itinerary]
			SET imITML = @itml,
				strDescription = @description,
				strModifiedBy = @modifiedby,
				dtModifiedTime = @modifiedtime,
				nStatus = @status
			WHERE nItinerarySetID = @key
			
		-- Clear and Re-Add rows to the Itinerary Service table
		DELETE FROM [dbo].[ItineraryService]
		WHERE
			@name = [ItineraryService].[ItineraryName]
			AND @major = [ItineraryService].[ItineraryMajorVersion]
			AND @minor = [ItineraryService].[ItineraryMinorVersion]
		INSERT INTO [dbo].[ItineraryService] ([ServiceUuid], [Name], [Type], [IsRequestResponse], [Position], [Stage], [Id], [NextId], [BusinessName],
				[ItineraryName],
				[ItineraryMajorVersion],
				[ItineraryMinorVersion],
				[ModifiedBy],
				[ModifiedOn])
			SELECT 
				Svc.value('(@uuid)[1]', 'NVARCHAR(50)') as 'ServiceUuid',
				Svc.value('(@name)[1]', 'NVARCHAR(100)') as 'ServiceName',
				Svc.value('(@type)[1]', 'NVARCHAR(50)') as 'Type',
				Svc.value('(@isRequestResponse)[1]', 'bit') as 'IsRequestResponse',
				Svc.value('(@position)[1]', 'int') as 'Position',
				Svc.value('(@stage)[1]', 'NVARCHAR(50)') as 'Stage',
				Svc.value('(@id)[1]', 'NVARCHAR(50)') as 'Id',
				Svc.value('(@nextId)[1]', 'NVARCHAR(50)') as 'NextId',
				Svc.value('(@businessName)[1]', 'NVARCHAR(250)') as 'BusinessName',
				@name as 'ItineraryName',
				@major as 'ItineraryMajor',
				@minor as 'ItineraryMinor',
				@modifiedby as 'ModifiedBy',
				@modifiedtime as 'ModifiedOn'
			FROM
				@itml.nodes('//Service') as Itin(Svc)

		RETURN @key
	END

-- must already be published, so complain
-- RETURN -1 
